package com.info.zhiduoduo.repository.mapper.report;

import com.info.zhiduoduo.repository.dto.company.CompanyTradeFlagDto;
import com.info.zhiduoduo.repository.dto.company.CompanyVerifyBriefDto;
import com.info.zhiduoduo.repository.dto.report.CountDetailDto;
import com.info.zhiduoduo.repository.dto.report.FinishedInventoryCountDto;
import com.info.zhiduoduo.repository.dto.report.StatisticCountDto;
import com.info.zhiduoduo.repository.dto.report.UnfinishedInvoiceCountDto;

import com.info.zhiduoduo.repository.entity.company.CompanyEntrustFile;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface ReportMapper {

	@Select(" SELECT COUNT(IF(DATE(ma.open_starttime) >= DATE(#{startDate}),1,NULL)) standardCount, " +
			" COUNT(IF(DATE(ma.open_starttime) < DATE(#{startDate}),1,NULL)) unstandardCount, " +
			" COUNT(IF(ma.identity_type=2,1,NULL)) hktwCount " +
			" FROM zdd_maker_account ma, zdd_maker_studio ms " +
			" WHERE ma.userid = ms.userid " +
			" AND ma.realname_auth >= 4 " +
			" AND DATE(ms.open_finishtime) = DATE(#{finishDate}) ")
	StatisticCountDto countOpenStudio(@Param("startDate") Date startDate, @Param("finishDate") Date finishDate);

	@Select(" SELECT COUNT(IF(DATE(ma.open_starttime) >= DATE(#{startDate}),1,NULL)) standardCount," +
			" COUNT(IF(DATE(ma.open_starttime) < DATE(#{startDate}),1,NULL)) unstandardCount " +
			" FROM zdd_maker_account ma " +
			" WHERE ma.realname_auth = 4 " +
			" AND ma.business_authentication_state = 1 and ma.temporarily_not_handled = 0")
	StatisticCountDto countUnopenStudio(@Param("startDate") Date startDate);

	@Select(" SELECT COUNT(IF(DATE(ms.open_tax_acct_starttime) >= DATE(#{startDate}),1,NULL)) standardCount, " +
			" COUNT(IF(DATE(ms.open_tax_acct_starttime) < DATE(#{startDate}),1,NULL)) unstandardCount, " +
			" COUNT(IF(ma.identity_type=2,1,NULL)) hktwCount " +
			" FROM zdd_maker_account ma, zdd_maker_studio ms, zdd_maker m " +
			" WHERE ma.userid = ms.userid and m.userid = ma.userid  " +
			" AND ms.state = 2 and m.state = 2 " +
			" AND DATE(ms.open_tax_acct_finishtime) = DATE(#{finishDate}) ")
	StatisticCountDto countOpenTaxAcct(@Param("startDate") Date startDate, @Param("finishDate") Date finishDate);

	@Select(" SELECT COUNT(IF(DATE(ms.open_tax_acct_starttime) >= DATE(#{startDate}),1,NULL)) standardCount," +
			" COUNT(IF(DATE(ms.open_tax_acct_starttime) < DATE(#{startDate}),1,NULL)) unstandardCount " +
			" FROM zdd_maker_studio ms, zdd_maker m " +
			" WHERE ms.userid = m.userid and ms.state = 2 and m.state = 2 " +
			" AND ISNULL(ms.open_tax_acct_finishtime) ")
	StatisticCountDto countUnopenTaxAcct (@Param("startDate") Date startDate);

	@Select(" SELECT COUNT(1) totalCount," +
			" COUNT(IF((ms.tax_acct_state = 2 and DATE(i.starttime) >= " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate}))) " +
			" or (ms.tax_acct_state = 3 and DATE(i.starttime) >= " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate}))),1,NULL)) standardCount," +
			" COUNT(IF((ms.tax_acct_state = 2 and DATE(i.starttime) < " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})))" +
			" or (ms.tax_acct_state = 3 and DATE(i.starttime) < " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate}))),1,NULL)) unstandardCount " +
			" from zdd_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" where i.state = 4 " +
			" and i.project_type = 1 " +
			" and DATE(i.finishtime) = DATE(#{finishDate})")
	StatisticCountDto countFinishedInvoice(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
	                                       @Param("unTaxAcctStartDate") Date unTaxAcctStartDate,
	                                       @Param("understockStartDate") Date understockStartDate, @Param("startDate") Date startDate,
	                                       @Param("finishDate") Date finishDate);

	@Select(" SELECT " +
			" COUNT(IF(ms.tax_acct_state = 2 and DATE(i.starttime) >= " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 2 and i.strategy = 0 and DATE(i.starttime) < " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctUnstandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and DATE(i.starttime) >= " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and i.strategy = 0 and DATE(i.starttime) < " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctUnstandardCount," +
			" COUNT(IF(i.invoicetype = 1, 1, NULl)) genCount," +
			" COUNT(IF(i.invoicetype = 2, 1, NULl)) speCount," +
			" COUNT(IF(i.strategy = 1 and i.strategy_date <= #{finishDate} and i.state =5 ,1,NULL)) strategyCount " +
			" from zdd_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" where i.state != 4 " +
			" and i.state != 6 " +
			" and i.project_type = 1 ")
	UnfinishedInvoiceCountDto countUnfinishedInvoice(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
	                                                 @Param("unTaxAcctStartDate") Date unTaxAcctStartDate,
	                                                 @Param("understockStartDate") Date understockStartDate, @Param("startDate") Date startDate,
	                                                 @Param("finishDate") Date finishDate);

	@Select(" SELECT COUNT(1) totalCount," +
			" COUNT(IF((ms.tax_acct_state = 2 and DATE(i.createtime) >= " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate}))) " +
			" or (ms.tax_acct_state = 3 and DATE(i.createtime) >= " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate}))),1,NULL)) standardCount," +
			" COUNT(IF((ms.tax_acct_state = 2 and DATE(i.createtime) < " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})))" +
			" or (ms.tax_acct_state = 3 and DATE(i.createtime) < " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate}))),1,NULL)) unstandardCount " +
			" from zdd_taxer_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" where i.state = 2 and DATE(i.confirmtime) = DATE(#{finishDate}) " +
			" and i.deleted = 0 ")
	StatisticCountDto countFinishedTaxerInvoice(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
	                                            @Param("unTaxAcctStartDate") Date unTaxAcctStartDate,
	                                            @Param("understockStartDate") Date understockStartDate, @Param("startDate") Date startDate,
	                                            @Param("finishDate") Date finishDate);

	@Select(" SELECT " +
			" COUNT(IF(ms.tax_acct_state = 2 and DATE(i.createtime) >= " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 2 and DATE(i.createtime) < " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctUnstandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and DATE(i.createtime) >= " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and DATE(i.createtime) < " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctUnstandardCount," +
			" COUNT(IF(i.invoice_type = 1, 1, NULl)) genCount," +
			" COUNT(IF(i.invoice_type = 2, 1, NULl)) speCount" +
			" from zdd_taxer_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" where i.state = 1 " +
			" and i.deleted = 0 ")
	UnfinishedInvoiceCountDto countUnfinishedTaxerInvoice(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
	                                                 @Param("unTaxAcctStartDate") Date unTaxAcctStartDate,
	                                                 @Param("understockStartDate") Date understockStartDate, @Param("startDate") Date startDate);

	@Select(" select IFNULL(SUM(IF(invoice_type = 1 and amount_type = 1, amount, 0)), 0) genTenThousand, " +
			" IFNULL(SUM(IF(invoice_type = 1 and amount_type = 2, amount, 0)), 0) genHundredThousand," +
			" IFNULL(SUM(IF(invoice_type = 2 and amount_type = 1, amount, 0)), 0) speTenThousand, " +
			" IFNULL(SUM(IF(invoice_type = 2 and amount_type = 2, amount, 0)), 0) speHundredThousand " +
			" from zdd_invoice_inventory_record " +
			" where state = 3" +
			" and deleted = 0 " +
			" and DATE(entry_time) = DATE(#{finishDate}) ")
	FinishedInventoryCountDto countInventory(@Param("finishDate") Date finishDate);

	@Select(" SELECT COUNT(*) " +
			" FROM zdd_company_auth_record car " +
			" WHERE DATE(createtime) = DATE(#{finishDate})")
	int countHandleCompanyVerify(@Param("finishDate") Date finishDate);

	@Select(" select COUNT(*) " +
			" from zdd_company_account ca " +
			" join zdd_company c on c.compid = ca.compid " +
			" where c.state = 2 " +
			" AND ca.realname_auth = 1  ")
	int countCompanyVerfiying();

	@Select(" select COUNT(*) " +
			" from zdd_maker_auth_record mar " +
			" where DATE(createtime) = DATE(#{finishDate})")
	int countHandleMakerVerify(@Param("finishDate") Date finishDate);

	@Select(" select COUNT(*) " +
			" from zdd_maker_account ma " +
			" join zdd_maker m on m.userid = ma.userid " +
			" where ma.realname_auth = 2 and ma.temporarily_not_handled = 0 ")
	int countMakerVerfiying();

	@Select(" select COUNT(*) " +
			" from zdd_maker_rescission_apply " +
			" where DATE(handletime) = DATE(#{finishDate}) ")
	int countHandleMakerRescissionApply(@Param("finishDate") Date finishDate);

	@Select(" select COUNT(*) " +
			" from zdd_maker_rescission_apply " +
			" where DATE(handletime) = DATE(#{finishDate}) " +
			" and state = 4")
	int countFinishMakerRescissionApply(@Param("finishDate") Date finishDate);

	@Select(" select i.userid,i.username,ma.realname,i.starttime,DATEDIFF(#{finishDate},i.starttime) as days," +
			" i.id, 1 as type,i.reasons_for_overdue " +
			" from zdd_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" join zdd_maker_account ma on ma.userid = i.userid " +
			" where ms.tax_acct_state = 2 " +
			" and DATE(i.starttime) < IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})) " +
			" and i.state != 4 " +
			" and i.state != 6 " +
			" AND i.project_type = 1 " +
			" UNION ALL " +
			" select i.userid,i.username,ma.realname,i.createtime AS starttime,DATEDIFF(#{finishDate},i.createtime) as days," +
			" i.invoiceid as id, 2 as type,i.reasons_for_overdue " +
			" from zdd_taxer_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" join zdd_maker_account ma on ma.userid = i.userid " +
			" where ms.tax_acct_state = 2 " +
			" and DATE(i.createtime) < IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})) " +
			" and i.state = 1 " +
			" and i.deleted = 0 ")
	List<CountDetailDto> listUnfinishUntaxAcctUnstandardInvoice(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
	                                                            @Param("unTaxAcctStartDate") Date unTaxAcctStartDate, @Param("finishDate") Date finishDate);

	@Select(" select i.userid,i.username,ma.realname,i.starttime,DATEDIFF(#{finishDate},i.starttime) as days, " +
			" i.id, 1 as type,i.reasons_for_overdue " +
			" from zdd_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" join zdd_maker_account ma on ma.userid = i.userid " +
			" where ms.tax_acct_state = 3 " +
			" and DATE(i.starttime) < IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})) " +
			" and i.state != 4 " +
			" and i.state != 6" +
			" AND i.project_type = 1 " +
			" AND i.strategy = 0 " +
			" UNION ALL " +
			" select i.userid,i.username,ma.realname,i.createtime AS starttime,DATEDIFF(#{finishDate},i.createtime) as days, " +
			" i.invoiceid as id, 2 as type,i.reasons_for_overdue " +
			" from zdd_taxer_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" join zdd_maker_account ma on ma.userid = i.userid " +
			" where ms.tax_acct_state = 3 " +
			" and DATE(i.createtime) < IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})) " +
			" and i.state = 1 " +
			" and i.deleted = 0")
	List<CountDetailDto> listUnfinishTaxAcctUnstandardInvoice(@Param("understockStartDate") Date understockStartDate,
	                                                     @Param("startDate") Date startDate, @Param("finishDate") Date finishDate);

	@Select(" SELECT ma.userid,ma.realname,ms.username,ma.open_starttime starttime,DATEDIFF(#{finishDate},ma.open_starttime) as days," +
			" ma.acctid as id, 5 as type,ma.un_open_studio_reasons_for_overdue as reasonsForOverdue " +
			" FROM zdd_maker_account ma " +
			" LEFT JOIN zdd_maker_studio ms ON ma.userid = ms.userid " +
			" WHERE ma.realname_auth = 4 " +
			" AND ma.business_authentication_state = 1 AND date ( ma.open_starttime ) < #{startDate} ")
	List<CountDetailDto> listUnopenStudio(@Param("startDate") Date startDate, @Param("finishDate") Date finishDate);

	@Select(" SELECT ma.userid,ma.realname,ms.username,ms.open_tax_acct_starttime starttime," +
			" DATEDIFF(#{finishDate},ms.open_tax_acct_starttime) as days," +
			" ms.userid as id, 6 as type,ms.reasons_for_overdue " +
			" FROM zdd_maker_studio ms " +
			" JOIN zdd_maker_account ma on ma.userid = ms.userid " +
			" JOIN zdd_maker m on m.userid = ms.userid " +
			" WHERE ms.state = 2 and m.state = 2 " +
			" AND DATE(ms.open_tax_acct_starttime) < DATE(#{startDate}) " +
			" AND ISNULL(ms.open_tax_acct_finishtime) ")
	List<CountDetailDto> listUnopenTaxAcct (@Param("startDate") Date startDate, @Param("finishDate") Date finishDate);

	@Select(" select COUNT(1) " +
			" from zdd_maker_account " +
			" where DATE_ADD(auth_submit_time, INTERVAL 1 DAY) <= NOW() " +
			" and auth_pass_time IS NULL " +
			" and realname_auth = 2 and temporarily_not_handled = 0 ")
	int countUnfinishMakerUnstandard(@Param("finishDate") Date finishDate);

	@Select(" select COUNT(1) " +
			" from zdd_company_account ca " +
			" join zdd_company c on c.compid = ca.compid " +
			" where DATE_ADD(auth_submit_time, INTERVAL 1 DAY) <= NOW() " +
			" and auth_pass_time IS NULL " +
			" and realname_auth = 1 " +
			" and c.state = 2 ")
	int countUnfinishCompanyUnstandard(@Param("finishDate") Date finishDate);

	@Select(" select userid,realname,auth_submit_time AS starttime," +
			" DATEDIFF(NOW(),auth_submit_time) as days,acctid as id, 4 as type,examine_maker_reasons_for_overdue as reasonsForOverdue " +
			" from zdd_maker_account " +
			" where DATE_ADD(auth_submit_time, INTERVAL 1 DAY) <= NOW() " +
			" and auth_pass_time IS NULL " +
			" and realname_auth = 2 ")
	List<CountDetailDto> listUnfinishMaker (@Param("finishDate") Date finishDate);

	@Select(" SELECT mak.userid as id,mak.userid,stu.username,acc.realname,stu.open_finishtime as starttime, " +
			" DATEDIFF(NOW(),stu.open_finishtime) as days, " +
			" stu.reasons_for_un_open_alipay as reasonsForOverdue " +
			" from zdd_maker mak " +
			" LEFT JOIN zdd_maker_account acc on mak.userid = acc.userid " +
			" left join zdd_maker_studio stu on mak.userid = stu.userid " +
			" left join zdd_company com on mak.bindcompid = com.compid " +
			" left join zdd_maker_alipay ali on mak.userid = ali.userid and ali.deleted=0 " +
			" where mak.createtime >= '2021-7-7' " +
			" and stu.state = 2 and mak.state != 4 " +
			" having days >= 3 ")
	List<CountDetailDto> listUnOpenAliPayMaker(@Param("finishDate") Date finishDate);

	@Select(" select ca.compid AS userid,c.compname as realname, ca.realname," +
			" DATEDIFF(NOW(),auth_submit_time) as days,ca.reasons_for_overdue, 3 as type, ca.acctid as id " +
			" from zdd_company_account ca " +
			" join zdd_company c on c.compid = ca.compid " +
			" where DATE_ADD(auth_submit_time, INTERVAL 1 DAY) <= NOW() " +
			" and auth_pass_time IS NULL " +
			" and c.state = 2 " +
			" and ca.realname_auth = 1 ")
	List<CountDetailDto> listUnfinishCompany (@Param("finishDate") Date finishDate);


	//查询 待开 发票  个数
	@Select(" select count(*) from zdd_invoice where " +
			" project_type = 1 and state in (1,2) " +
			" AND IF (strategy = 1 AND state = 1,DATE(strategy_date ) <= CURDATE( ),1 = 1 ) ")
	int toBeAllInvoiceCount();

	//查询 待开 普票  个数
	@Select(" select count(*) from zdd_invoice " +
			" where project_type = 1 and state in (1,2) and invoicetype = 1 " +
			" AND IF (strategy = 1 AND state = 1,DATE(strategy_date ) <= CURDATE( ),1 = 1 )")
	int toBeGeneralInvoiceCount();

	//查询 待开 专票 个数
	@Select(" select count(*) from zdd_invoice " +
			" where project_type = 1 and state in (1,2) and invoicetype = 2 " +
			" AND IF (strategy = 1 AND state = 1,DATE(strategy_date ) <= CURDATE( ),1 = 1 )")
	int toBeSpecialInvoiceCount();


	//查询 待开智众包发票 个数
	@Select(" select count(*) from zdd_invoice where project_type = 2 and state != 4 ")
	int toBeCrowdSourceInvoiceCount();

	//查询 待开成本发票 个数
	@Select(" SELECT count(*) FROM zdd_crowdsource_invoice zci " +
			" WHERE  zci.state = 1   AND zci.user_type = 2")
	int toBeCostInvoiceCount();



	//查询 待开 发票 个数
	@Select(" select count(*) from zdd_taxer_invoice ti" +
			" left join zdd_maker m on m.userid = ti.userid " +
			" where ti.state != 2 and m.maker_type = 3 and ti.deleted = 0 ")
	int toBeTaxerInvoiceCount();


	//查询 纯代理待开票  个数
	@Select(" select count(*) from zdd_taxer_invoice ti" +
			" left join zdd_maker m on m.userid = ti.userid " +
			" where ti.state != 2 and m.maker_type = 1 and ti.deleted = 0 ")
	int toBePureAgencyInvoiceCount();

	//查询 嘉银待开票  个数
	@Select(" select count(*) from zdd_foreign_invoice where merge_id = 0 and whether_merge = 0 and state = 1 ")
	int toBeJiaYinInvoiceCount();

	//查询 嘉银合并待开票  个数
	@Select(" select count(*) from zdd_foreign_invoice where whether_merge = 1 and state != 3 ")
	int toBeMergeJiaYinInvoiceCount();

	//查询 待审核合伙人  个数
	@Select(" select count(*) from zdd_partner p " +
			" left join zdd_partner_account pa on pa.partnerid = p.partnerid " +
			" where pa.realname_auth = 2 and p.deleted = 0 and p.state != 4")
	int toBeReviewedPartnerCount();

	//查询 待审核企业  个数
	@Select(" select count(*) from zdd_partner_recommend_company where state = 1 ")
	int toBeReviewedCompanyCount();

	//已到期未停止分润合伙人个数
	@Select(" select count(*) from zdd_partner_company pc " +
			" join zdd_partner p on p.partnerid = pc.partnerid " +
			" join zdd_partner_account pa on pa.partnerid = p.partnerid " +
			" join zdd_company c on c.compid = pc.compid " +
			" where pc.state = 1 and pc.expiredate <= CURDATE() and pc.deleted = 0 ")
	int expiredButNotStoppedCount();


	//待审核 工作室个数
	@Select(" select count(*) from zdd_maker_account ua " +
			" join zdd_maker u on u.userid=ua.userid " +
			" where ua.realname_auth = 2 AND ua.temporarily_not_handled = 0 and u.maker_type = 2 ")
	int toBeReviewedTaxerStudioCount();


	//待工作室付款个数
	@Select(" select count(*) from zdd_taxer_payment tp " +
			" join zdd_maker_account ma on ma.userid = tp.userid " +
			" left join zdd_taxer_invoice ti on ti.invoiceid = tp.invoiceid and ti.deleted = 0 " +
			" left join zdd_maker_studio ms on ms.userid = tp.userid " +
			" join zdd_maker m on m.userid = tp.userid " +
			" where tp.state = 1 and tp.deleted = 0 ")
	int toBePaidTaxerStudioCount();

	//待回复投诉个数
	@Select(" select count(*) from zdd_complaint where need_reply = 1 and has_reply = 0 and deleted = 0")
	int toBeAnsweredComplaintCount();

	@Select("select count(*) from zdd_value_serve where state=0")
	int toBeAppreciationServiceCount();

	//主要取  项目 标准内未开税户(首次交易)待开数,标准外未开税户(首次交易)待开数，标准内已开税户(非首次交易)待开数,,标准外已开税户(非首次交易)待开数
	@Select(" SELECT  " +
			" COUNT(IF(ms.tax_acct_state = 2 and DATE(i.starttime) >=  " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 2 and i.strategy = 0 and DATE(i.starttime) <  " +
			" IF(understock = 1, DATE(#{understockUnTaxAcctStartDate}), DATE(#{unTaxAcctStartDate})),1,NULL)) unTaxAcctUnstandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and DATE(i.starttime) >=  " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctStandardCount," +
			" COUNT(IF(ms.tax_acct_state = 3 and i.strategy = 0 and DATE(i.starttime) <  " +
			" IF(understock = 1, DATE(#{understockStartDate}), DATE(#{startDate})),1,NULL)) taxAcctUnstandardCount " +
			" from zdd_invoice i " +
			" join zdd_maker_studio ms on ms.userid = i.userid " +
			" where i.state in (1,2) " +
			" and i.project_type = 1 " +
			" and IF (  i.strategy = 1 AND  i.state = 1, DATE(  i.strategy_date ) <= CURDATE( ), 1 = 1 )")
	UnfinishedInvoiceCountDto toBeInvoiceFirstOrNotFirstCount(@Param("understockUnTaxAcctStartDate") Date understockUnTaxAcctStartDate,
													 @Param("unTaxAcctStartDate") Date unTaxAcctStartDate,
													 @Param("understockStartDate") Date understockStartDate, @Param("startDate") Date startDate);

	/**
	 *  待开票数量
	 *
	 * @return
	 */
	@Select(" select count(1) from zdd_invoice i join zdd_company c on c.compid = i.compid " +
			" join zdd_company_account ca on ca.compid = i.compid where i.state = 1  and i.project_type in(1,4,5) ")
	Integer getZddWaitInvokeSum();

	/**
	 * 智众包待开票数量
	 *
	 * @return
	 */
	@Select(" select count(0) from zdd_invoice i join zdd_company c on c.compid = i.compid " +
			" join zdd_company_account ca on ca.compid = i.compid where  i.state = 2 and i.project_type in(2,6) ")
	Integer getCrowdsourceWaitInvokeSum();

	/**
	 * 节税侠待开票数量
	 *
	 * @return
	 */
	@Select(" select count(1) from zdd_taxer_invoice ti join zdd_maker_studio ms on ms.userid = ti.userid " +
			" join zdd_maker m on m.userid = ti.userid  where ti.state=1 and ti.deleted = 0 ")
	Integer getTaxerWaitInvokeSum();

	/**
	 * 成本发票（创客）待开票数量
	 *
	 * @return
	 */
	@Select(" select count(1) FROM zdd_crowdsource_invoice zci " +
			" LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id  " +
			" LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid " +
			" LEFT JOIN zdd_company c ON c.compid = csp.compid " +
			" LEFT JOIN zdd_maker_account zma ON zma.userid = cspi.userid AND cspi.user_type = 2 " +
			" LEFT JOIN zdd_maker_studio zms ON zms.userid = cspi.userid AND cspi.user_type = 2 " +
			" where  zci.state = 1 AND csp.state = 4  AND csp.deleted = 0 " +
			" AND zci.id IS NOT NULL and cspi.user_type = 2 and  c.compname like '%%' and CONCAT(zma.realname,zms.username) like '%%' ")
	Integer getGongQingInvoiceMakerSum();

	/**
	 * 成本发票（自然人）待开票数量
	 * @return
	 */
	@Select(" select count(1) FROM zdd_crowdsource_invoice zci " +
			" LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id " +
			" LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid " +
			" LEFT JOIN zdd_company c ON c.compid = csp.compid " +
			" LEFT JOIN zdd_crowdsource_account zca ON zca.crowdsourceid = zci.crowdsourceid AND cspi.user_type = 1 " +
			" where  zci.state = 1 AND csp.state = 4  AND csp.deleted = 0 " +
			" AND zci.id IS NOT NULL and  c.compname like '%%' and zca.realname like '%%'  and cspi.user_type = 1 ")
	Integer getGongQingInvoiceNaturalSum();

	/**
	 * 成本发票（合并）待开票数量
	 * @return
	 */
	@Select(" select count(1) FROM zdd_crowdsource_invoice " +
			" where state = 2 AND whether_merge = 1 ")
	Integer getGongQingInvoiceMergeSum();

	/**
	 * 特殊需求或承诺未解决
	 * @return
	 */
	@Select(" SELECT count(1) FROM zdd_platform_memorandum pm WHERE state=1")
	Integer getMemorandumByConditionSum();

	/**
	 * 增值服务待审核数量
	 * @return
	 */
	@Select(" select count(1) from zdd_value_serve vs where vs.state = 0 ")
	Integer getAllServeSum();

	/**
	 * 异常事务记录未解决数量
	 * @return
	 */
	@Select(" select count(1) from zdd_service_error err " +
			" left join zdd_system_manager m on err.create_person=m.managerid " +
			" where wheather_solve=0 and content like '%%' and m.managername like '%%'")
	Integer getServiceErrorSum();

	/**
	 * 创客团申请中划款数量
	 * @return
	 */
	@Select("select count(0) from zdd_company_transfer_maker_groupon where state=1")
	Integer getRechargeApplySum();

	@Select("SELECT count(1) from ( " +
			"  select c.compid,CASE WHEN cef.entrust_end_time is null THEN 1 when cef.entrust_end_time < now() then 3 ELSE 2 END as entrustFileState " +
			"    from zdd_company c  " +
			"    left join (select compid,MAX(entrust_end_time) AS entrust_end_time,entrust_file,entrust_start_time from zdd_company_entrust_file where state = 1 and deleted = 0 GROUP BY compid ORDER BY entrust_end_time desc) cef on c.compid = cef.compid  " +
			"    where c.state = 4 " +
			"    and c.intel_crowdsource = 2 " +
			")tem where entrustFileState in (1,3)")
	int contractQuantityNotUploaded();

	@Select("SELECT sum(case when trade_flag = 0 then 1 else 0 end) as tradeFlagNum0, " +
			" sum(case when trade_flag = 1 then 1 else 0 end) as tradeFlagNum1," +
			" sum(case when trade_flag = 2 then 1 else 0 end) as tradeFlagNum2 " +
			" FROM  zdd_company c " +
			" JOIN zdd_company_account ca on ca.compid = c.compid " +
			" WHERE c.state = 2")
	CompanyTradeFlagDto companyTradeQuantity();

}
